﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Configuration;

namespace ZealousTTS.Domain
{
    public class UtilityDAO
    {
        private static String _connectionString;
        public static String ConnectionString
        {
            get { return _connectionString; }
            set { _connectionString = value; }
        }

        public UtilityDAO()
        {
        }
        /// <summary>
        /// Gets DataSet by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL">The SQL String or the Name of the Stored Procedure</param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public DataSet GetDataSet(String SQL, DbParameter[] parameters, bool isStoredProcedure)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            DataSet ds = new DataSet("CustomeQueryResult");
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                SqlCommand command = new SqlCommand(SQL, conn);
                command.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));//command.Parameters.Add(parameter);
                }
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            return ds;
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL String Equiped with Page Number PlaceHolders.
        /// Placeholder should be given for Start Record and End Record as {0} -> Start Record and {1} -> End Record
        /// </summary>
        /// <param name="SQL">The SQL String</param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        public DataSet GetPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord)
        {
            return getPagedData(SQL, parameters, pageNumber, pageSize, out totalRecord, false);
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL">Name of the Stored Procedure or the Full SQL Statement with the Placeholders for Start Record and End Record</param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public DataSet GetPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord, bool isStoredProcedure)
        {
            return getPagedData(SQL, parameters, pageNumber, pageSize, out totalRecord, isStoredProcedure);
        }
        /// <summary>
        /// Gets Paged Data by Executing a SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="parameters"></param>
        /// <param name="pageNumber"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns></returns>
        private DataSet getPagedData(String SQL, DbParameter[] parameters, int pageNumber, int pageSize, out int totalRecord, bool isStoredProcedure)
        {
            totalRecord = 0;
            int startRecord = (pageSize * (pageNumber - 1)) + 1;
            int endRecord = pageSize * pageNumber;            

            SqlConnection conn = new SqlConnection(ConnectionString);
            DataSet ds = new DataSet("ResultSet");
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                SqlCommand command = new SqlCommand();
                command.Connection = conn;
                if (isStoredProcedure)
                {
                    command.Parameters.AddWithValue("@StartRecord", startRecord);
                    command.Parameters.AddWithValue("@EndRecord", endRecord);
                    command.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    SQL = string.Format(SQL, startRecord, endRecord);
                    command.CommandType = CommandType.Text;
                }
                command.CommandText = SQL;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                    {
                        command.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));
                    }
                }                
                SqlDataAdapter da = new SqlDataAdapter(command);
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                    conn.Close();
            }
            if (ds != null)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    object data = ds.Tables[0].Rows[0]["TotalRecord"];
                    totalRecord = ((data == null) || (data == DBNull.Value) || (data.ToString() == string.Empty)) ? 0 : Convert.ToInt32(data);                    
                }
            }
            return ds;
        }

        /// <summary>
        /// Gets Scalar Value Object by Executing an SQL Statement or a Stored Procedure
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="parameters"></param>
        /// <param name="isStoredProcedure"></param>
        /// <returns></returns>
        public object GetScalar(String SQL, DbParameter[] parameters, bool isStoredProcedure)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            try
            {
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                SqlCommand command = new SqlCommand(SQL, conn);
                command.CommandType = isStoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
                if (parameters != null && parameters.Count() > 0)
                {
                    foreach (DbParameter parameter in parameters)
                        command.Parameters.Add(new SqlParameter(parameter.Name, parameter.Value));//command.Parameters.Add(parameter);
                }
                return command.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
            }
            //return null;
        }        
    }
}
